從上個章節可以知道在SELECT語句中後面可以接的從句(clause),在本章節中將會繼續介紹其他的從句。
GROUP BY
從句是用來SELECT
查詢語句的聚合模式,其運作的方式如下:
GROUP BY
從句包含了單一或是多個描述式,這個清單扮演的是「群組鍵」。當每個描述式可以參考成為「鍵之描述」(key expressions)。SELECT
、HAVING
與ORDER BY
從句必須以鍵之描述、非鍵之描述上用聚合函數為基礎來進行計算。換句話說,每個從資料表中選擇到的欄位必須在鍵之描述或是聚合函數中用到,上述兩者其一,並非兩個都需要使用。SELECT
查詢語句結果會從來源資料表指定的群組鍵當作獨特的欄位,並只會出現一次,分組之後進行計算後得到查詢結果後的資料表出來。enable_positional_arguments
之設定。對於空值的處理,ClickHouse會將NULL空值當作值來處理,即會判斷NULL==NULL
,下列是有關於此空值處理的範例:
┌─x─┬────y─┐
│ 1 │ 2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │ 2 │
│ 3 │ 3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘
針對上述的資料表執行SELECT sum(x), y FROM t_null_big GROUP BY y
SQL敘述之後,會得到下列的查詢結果:
┌─sum(x)─┬────y─┐
│ 4 │ 2 │
│ 3 │ 3 │
│ 5 │ ᴺᵁᴸᴸ │
└────────┴──────┘
由此可見,針對y欄位進行分組,NULL空值也是一組,透過SUM(x)
的聚合函數運算之後,NULL這一組的x
欄位的總和為5。
ROLLUP是用來計算鍵描述的部分小計(subtotals),這個會以GROUP BY
清單中的順序進行決定,部分總計的筆數的欄位會顯示在結果資料表中最後一欄欄位。
部分小計會以相反的順序計算,首先為欄位中的最後一個鍵表達式計算小計,接著為前一個鍵表達式計算小計,依此類推,直到到第一個鍵表達式為止。
將在部分小計的行中,會將已經分組的鍵表達式的值設置為0或空行。
考慮下面的資料表:
┌─year─┬─month─┬─day─┐
│ 2019 │ 1 │ 5 │
│ 2019 │ 1 │ 15 │
│ 2020 │ 1 │ 5 │
│ 2020 │ 1 │ 15 │
│ 2020 │ 10 │ 5 │
│ 2020 │ 10 │ 15 │
└──────┴───────┴─────┘
接著我們使用下列的SQL查詢:
SELECT year, month, day, count(*) FROM t GROUP BY ROLLUP(year, month, day);
從上述的SQL查詢中可以得知,GROUP BY
有三個鍵表達式,結果會有四個資料表,已由右至左為:
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │ 10 │ 15 │ 1 │
│ 2020 │ 1 │ 5 │ 1 │
│ 2019 │ 1 │ 5 │ 1 │
│ 2020 │ 1 │ 15 │ 1 │
│ 2019 │ 1 │ 15 │ 1 │
│ 2020 │ 10 │ 5 │ 1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │ 1 │ 0 │ 2 │
│ 2020 │ 1 │ 0 │ 2 │
│ 2020 │ 10 │ 0 │ 2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │ 0 │ 0 │ 2 │
│ 2020 │ 0 │ 0 │ 4 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 0 │ 0 │ 6 │
└──────┴───────┴─────┴─────────┘
CUBE會用來計算在GROUP BY
清單中,針對鍵描述式的每個組合小計,小計的欄位會放在每個結果的資料表最後面。
考慮下列的資料表:
┌─year─┬─month─┬─day─┐
│ 2019 │ 1 │ 5 │
│ 2019 │ 1 │ 15 │
│ 2020 │ 1 │ 5 │
│ 2020 │ 1 │ 15 │
│ 2020 │ 10 │ 5 │
│ 2020 │ 10 │ 15 │
└──────┴───────┴─────┘
接著使用的SQL查詢如下:
SELECT year, month, day, count(*) FROM t GROUP BY CUBE(year, month, day);
從上述的SQL查詢得知,會有8個結果的資料表,GROUP BY
的組合如下:
GROUP BY year, month, day
GROUP BY year, month
(day欄位填成0)GROUP BY year, day
(month欄位填成0)GROUP BY year
(month與day欄位填成0)GROUP BY month, day
(year欄位填成0)GROUP BY month
(year與day欄位填成0)GROUP BY day
(year與month欄位填成0)year
、month
與day
欄位都填成0。相關輸出的結果資料表如下:
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │ 10 │ 15 │ 1 │
│ 2020 │ 1 │ 5 │ 1 │
│ 2019 │ 1 │ 5 │ 1 │
│ 2020 │ 1 │ 15 │ 1 │
│ 2019 │ 1 │ 15 │ 1 │
│ 2020 │ 10 │ 5 │ 1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │ 1 │ 0 │ 2 │
│ 2020 │ 1 │ 0 │ 2 │
│ 2020 │ 10 │ 0 │ 2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │ 0 │ 5 │ 2 │
│ 2019 │ 0 │ 5 │ 1 │
│ 2020 │ 0 │ 15 │ 2 │
│ 2019 │ 0 │ 15 │ 1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │ 0 │ 0 │ 2 │
│ 2020 │ 0 │ 0 │ 4 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 1 │ 5 │ 2 │
│ 0 │ 10 │ 15 │ 1 │
│ 0 │ 10 │ 5 │ 1 │
│ 0 │ 1 │ 15 │ 2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 1 │ 0 │ 4 │
│ 0 │ 10 │ 0 │ 2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 0 │ 5 │ 3 │
│ 0 │ 0 │ 15 │ 3 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 0 │ 0 │ 6 │
└──────┴───────┴─────┴─────────┘
而我們也可以把上述的SQL查詢語句寫成如下SQL:
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE;
若在GROUP BY
從句使用了GROUPING SETS
的話,則是定義分組的集合,與上述的ROLLUP
與CUBE
可以寫成等價的SQL語句,相關的範例如下:
-- SQL查詢1
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;
-- SQL查詢2
SELECT year, month, day, count(*) FROM t GROUP BY
GROUPING SETS
(
(year, month, day),
(year, month),
(year),
()
);
從上面的SQL查詢範例可以知道,SQL查詢1可以等價於SQL查詢2的SQL語句。
LIMIT n BY expressions
之從句可以讓SQL語句的結果對於某個欄位之獨特值分別輸出前n筆資料,相關的語法如下所示:
以下是一個關於此從句的範例,首先先利用下列的SQL來建立一個資料表:
CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;
INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);
透過ClickHouse客戶端執行上述的SQL的輸出訊息如下:
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;
CREATE TABLE limit_by
(
`id` Int,
`val` Int
)
ENGINE = Memory
Query id: 93bcc88c-d2f7-443e-980a-d5ca5e8f3bb2
Ok.
0 rows in set. Elapsed: 0.007 sec.
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);
INSERT INTO limit_by FORMAT Values
Query id: 4fb2c151-94fb-450b-8d7b-49cc9b214b39
Ok.
5 rows in set. Elapsed: 0.004 sec.
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)
上述的SQL是建立一個將資料存放到記憶體的資料表,這將會是一個暫時的資料表,接著寫入五筆資料進去,建立好資料表之後,執行下列的SQL:
SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id
執行上述的SQL所得到的訊息如下:
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id
SELECT *
FROM limit_by
ORDER BY
id ASC,
val ASC
LIMIT 2 BY id
Query id: 5dc088d6-4c05-4b15-8d5f-0eb0764712c6
┌─id─┬─val─┐
│ 1 │ 10 │
│ 1 │ 11 │
│ 2 │ 20 │
│ 2 │ 21 │
└────┴─────┘
4 rows in set. Elapsed: 0.002 sec.
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)
從上述執行的SQL可以知道,以id欄位來說,獨特的id有1與2,因此查詢結果的資料表為分別輸出各id為1與2的前2筆資料出來。
接著執行下列的SQL:
SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id
執行上述的SQL並輸出的訊息如下:
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id
SELECT *
FROM limit_by
ORDER BY
id ASC,
val ASC
LIMIT 1, 2 BY id
Query id: 8e37086f-10e8-473f-b35e-904670200f86
┌─id─┬─val─┐
│ 1 │ 11 │
│ 1 │ 12 │
│ 2 │ 21 │
└────┴─────┘
3 rows in set. Elapsed: 0.002 sec.
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)
從上述的輸出訊息來看,針對id欄位有1與2這兩個獨特的值,因為加入了OFFSET的值為1,因此需要忽略1筆資料後的前2筆資料出來,因此以id欄位中的1的資料來說,(1, 10)
之這筆資料會忽略,接著印出(1, 11)
與(1, 12)
這兩筆資料出來,(2, 20)
這筆資料會忽略,接著因為只剩一筆資料,因此印出前兩筆資料出來只會印出(2, 21)
這筆資料。
HAVING從句允許過濾由GROUP BY
從句產生的聚合SQL查詢結果,其類似於WHERE
從句,但不同的是WHERE
從句會在進行聚合計算之前執行,而HAVING
從句會在聚合計算之後執行。
如果聚合函數沒有使用的話,則HAVING從句無法使用,這時候可以改用WHERE
從句。
從本章節中,我們可以知道SELECT語句中的GROUP BY
、LIMIT BY
與HAVING
從句的用法,下一章節中將會其他SELECT語句中的從句用法介紹。